package cn.mothin.frame.util;

import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import cn.mothin.frame.core.bean.SearchData;
import cn.mothin.frame.core.interfaces.vo.IQueryPage;

public class SqlUtil {

    public static String changeToSelectCount(String sql) {
        if (sql.toLowerCase().contains("group")) {
            return "SELECT COUNT(*) FROM (" + removeOrders(sql) + " ) t";
        }
        return "SELECT COUNT(*) " + removeSelect(SqlUtil.removeOrders(sql));
    }

    public static String removeOrders(String hql) {
        if (StringUtil.isNullOrEmpty(hql)) {
            return hql;
        }
        Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", 2);
        Matcher m = p.matcher(hql);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            m.appendReplacement(sb, "");
        }
        m.appendTail(sb);
        return sb.toString();
    }

    public static String removeSelect(String hql) {
        if (StringUtil.isNullOrEmpty(hql)) {
            return hql;
        }
        int beginPos = hql.toLowerCase().indexOf("from");
        if (beginPos == -1) {
            return hql;
        }
        return hql.substring(beginPos);
    }

    public static String addOrders(String hql, IQueryPage page) {
        if (StringUtil.isNullOrEmpty(page.getSort())
                || StringUtil.isNullOrEmpty(page.getOrder())) {
            return hql;
        }
        return removeOrders(hql) + " ORDER BY " + page.getSort() + " "
                + page.getOrder();
    }

    public static String reorganizeSQL(String sql, IQueryPage page,
            List<Object> params) {
        String newSql = removeOrders(sql);
        if (page.getSearch() != null && page.getSearch().size() > 0) {
            newSql = "SELECT * FROM (" + newSql + ") t ";
            if (page.getSearch() != null && page.getSearch().size() > 0) {
                newSql += "WHERE TRUE ";
                for (String key : page.getSearch().keySet()) {
                    SearchData data = page.getSearch().get(key);
                    switch (data.getT()) {
                    case "string":
                        String v = data.getV().toString();
                        if (!StringUtil.isNullOrEmpty(v)) {
                            newSql += "AND " + key + " LIKE ? ESCAPE '/' ";
                            params.add(StringUtil.makeFuzzyQuery(v));
                        }
                        break;
                    case "numeric":
                        if (data.getMin() != null) {
                            newSql += "AND " + key + ">=? ";
                            params.add(data.getMin());
                        }
                        if (data.getMax() != null) {
                            newSql += "AND " + key + "<? ";
                            params.add(data.getMax());
                        }
                        break;
                    case "date":
                        if (data.getMin() != null) {
                            newSql += "AND " + key + ">=? ";
                            params.add(data.getMin());
                        }
                        if (data.getMax() != null) {
                            newSql += "AND " + key + "<? ";
                            params.add(data.getMax());
                        }
                        break;
                    default:
                        if (data.getV() != null) {
                            newSql += "AND " + key + " = ? ";
                            params.add(data.getV());
                        }
                        break;
                    }
                }
            }
        }

        if (!StringUtil.isNullOrEmpty(page.getSort())
                && !StringUtil.isNullOrEmpty(page.getOrder())) {
            newSql += " ORDER BY " + page.getSort() + " " + page.getOrder();
        }
        return newSql;
    }
}
